﻿Imports System.Globalization
Imports System.Text
Imports System.Reflection
Imports System.IO

Public Module ExportBible
	Public Sub ExportToMDBBBL(Bible As Bible.VersesDataTable, LanguageCode1 As String, FileLocation As String, BibleName As String)

		Using newDatabase As Stream = New FileStream(FileLocation, FileMode.CreateNew)
			Assembly.GetExecutingAssembly().GetManifestResourceStream("NWTCompiler.template.mdb").CopyTo(newDatabase)
		End Using

		Dim connectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=""{0}"";", FileLocation)

		Using conn As New OleDb.OleDbConnection(connectionString)
			conn.Open()

			Dim cmd As OleDb.OleDbCommand
			Dim sql As String

			Using trans As OleDb.OleDbTransaction = conn.BeginTransaction

				For Each verse As Bible.VersesRow In Bible
					sql = "INSERT INTO Bible ([Book ID], Chapter, Verse, Scripture) VALUES (?, ?, ?, ?)"
					cmd = New OleDb.OleDbCommand(sql, conn, trans)
					cmd.Parameters.AddWithValue("[Book ID]", verse.BookNumber)
					cmd.Parameters.AddWithValue("Chapter", verse.Chapter)
					cmd.Parameters.AddWithValue("Verse", verse.Verse)
					cmd.Parameters.AddWithValue("Scripture", verse.Text.UnicodeToASCII)

					cmd.ExecuteNonQuery()
				Next

				trans.Commit()
			End Using

			sql = String.Format("UPDATE Details SET Abbreviation = '{0}'", BibleName.ToUpperInvariant)
			cmd = New OleDb.OleDbCommand(sql, conn)
			cmd.ExecuteNonQuery()

		End Using

	End Sub

	Public Sub ExportToBBLXSQLite(Bible As Bible.VersesDataTable, BibleBaseURL As String, FileLocation As String, BibleName As String)

		Using newDatabase As Stream = New FileStream(FileLocation, FileMode.CreateNew)
			Assembly.GetExecutingAssembly().GetManifestResourceStream("NWTCompiler.template.sqlite").CopyTo(newDatabase)
		End Using

		Dim connectionString As String = String.Format("Data Source=""{0}""", FileLocation)

		Using conn As New SQLite.SQLiteConnection(connectionString)
			conn.Open()

			Dim cmd As SQLite.SQLiteCommand
			Dim sql As String

			Using trans As SQLite.SQLiteTransaction = conn.BeginTransaction

				For Each verse As Bible.VersesRow In Bible
					sql = "INSERT INTO Bible (Book, Chapter, Verse, Scripture) VALUES (?, ?, ?, ?)"
					cmd = New SQLite.SQLiteCommand(sql, conn, trans)
					cmd.Parameters.AddWithValue("Book", verse.BookNumber)
					cmd.Parameters.AddWithValue("Chapter", verse.Chapter)
					cmd.Parameters.AddWithValue("Verse", verse.Verse)
					cmd.Parameters.AddWithValue("Scripture", verse.Text.UnicodeToASCII)

					cmd.ExecuteNonQuery()
				Next

				trans.Commit()
			End Using

			sql = String.Format("UPDATE Details SET Abbreviation = '{0}'", BibleName.ToUpperInvariant)
			cmd = New SQLite.SQLiteCommand(sql, conn)
			cmd.ExecuteNonQuery()

			sql = String.Format("UPDATE Details SET RightToLeft = '{0}'", AvailableLanguages.Find(Function(x) x.Key = BibleBaseURL).Value.RightToLeft.ToString)
			cmd = New SQLite.SQLiteCommand(sql, conn)
			cmd.ExecuteNonQuery()

			sql = String.Format("UPDATE Details SET OT = '{0}'", AvailableLanguages.Find(Function(x) x.Key = BibleBaseURL).Value.Hebrew.ToString)
			cmd = New SQLite.SQLiteCommand(sql, conn)
			cmd.ExecuteNonQuery()

			sql = String.Format("UPDATE Details SET NT = '{0}'", AvailableLanguages.Find(Function(x) x.Key = BibleBaseURL).Value.Greek.ToString)
			cmd = New SQLite.SQLiteCommand(sql, conn)
			cmd.ExecuteNonQuery()

			cmd.Dispose()

		End Using

	End Sub

End Module
